package com.easyattendance.services;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.easyattendance.model.AllTimeSlots;
import com.easyattendance.model.Proxy;
import com.easyattendance.model.TimeSlot;
import com.easyattendance.model.TimeTable;
import com.easyattendance.util.HibernateUtil;

public class AddAllTimeSlotsService {

	/* Method to CREATE a AllTimeSlots in the database */
	   public synchronized boolean addAllTimeSlots(AllTimeSlots allTimeSlots){
	      Session session = HibernateUtil.openSession();
	      Transaction tx = null;
	      
	      try{
	         tx = session.beginTransaction();
	         session.save(allTimeSlots);
	         tx.commit();
	      }catch (HibernateException e) {
	         if (tx!=null) tx.rollback();
	         e.printStackTrace(); 
	      }finally {
	         session.close(); 
	      }
	      return true;
	   }
	   
	   /* Method to  READ all the alltimeslots values */
	   public String[] listAllTimeSlotsBySection(String sectionEmailId) {
		   Session session = HibernateUtil.openSession();
		   
		   AddSectionService sectionService = new AddSectionService();
		   String sectionId = sectionService.getSectionBySectionEmail(sectionEmailId).getSection_id();
				   
	      Transaction tx = null;
	      List timeSlots = null;
	      String[] timeSlotsMap = new String[10];
	      TimeSlot timeSlotsSettings = null;
	      
	      int i = 0;
	      try{
	         tx = session.beginTransaction();
	         timeSlots = session.createQuery("FROM AllTimeSlots where section_id='"+sectionId+"'").list();
	         
	         for (Iterator iterator = timeSlots.iterator(); iterator.hasNext();){
	        	 AllTimeSlots section = (AllTimeSlots) iterator.next();
	        	 timeSlotsMap[i++] = section.getTime_slot();
	        	 System.out.print("timeslot: " + section.getTime_slot());
	         }
	         tx.commit();
	      }catch (HibernateException e) {
	         if (tx!=null) tx.rollback();
	         e.printStackTrace(); 
	      }finally {
	         session.close(); 
	      }
	      return timeSlotsMap;
	   }
	   
	  
	   
	   /* Method to  READ all the alltimeslots values of a section and semester which are not occupied for the day passed*/
	   public List listAllUnoccupiedTimeSlots(String sectionId, String day, String semYearId) {
		  Session session = HibernateUtil.openSession();
	      Transaction tx = null;
	      List timeSlots = new ArrayList();
	      List timeTable = new ArrayList();
	      List timeTableSlots = new ArrayList();
	      List unOccupiedTimeSlots = new ArrayList(); 
	      List newUnOccupiedTimeSlots = new ArrayList();
	      
	      boolean flag = false;
	      
	      TimeSlot timeSlotsSettings = null;
	      
	      try{
	         tx = session.beginTransaction();
	         timeSlots = session.createQuery("FROM AllTimeSlots where section_id='"+sectionId+"'").list();
	         timeTable = session.createQuery("FROM TimeTable where section_id='"+sectionId+"' and class_id='"+semYearId+"' and day_of_week='"+day+"'").list();
	         timeSlotsSettings = (TimeSlot) session.createQuery("FROM TimeSlot where section_id='"+sectionId+"'").uniqueResult();
	         
	         for(Iterator itr = timeTable.iterator(); itr.hasNext(); ) {
    			 TimeTable TT = (TimeTable) itr.next();
    			 String tmpTime = TT.getTime_slot().getHours()+":"+TT.getTime_slot().getMinutes();
    			 timeTableSlots.add(tmpTime);
    			 System.out.println("timeslot Name(occupied): " + tmpTime); 
	         }
	         for(Iterator itr = timeTableSlots.iterator(); itr.hasNext(); ) {
	        	 String TT =  (String) itr.next();
	        	 System.out.println("=======timeslot Name(occupied): " + TT);
	         }
	         
	         
	         if(timeTable.isEmpty()) {
	        	 for (Iterator iterator = timeSlots.iterator(); iterator.hasNext();){
		        	 AllTimeSlots tt = (AllTimeSlots) iterator.next();
		        	 
		        	 String recess1Time = timeSlotsSettings.getRecess1_time().getHours()+":"+timeSlotsSettings.getRecess1_time().getMinutes();
	        		 String recess2Time = timeSlotsSettings.getRecess2_time().getHours()+":"+timeSlotsSettings.getRecess2_time().getMinutes();
		        	 
		        	 if( !tt.getTime_slot().equals(recess1Time) && !tt.getTime_slot().equals(recess2Time) ) {
		        		 unOccupiedTimeSlots.add(tt.getTime_slot());
		        	 }
	        	 }
	         }
	         else {
	        	 for (Iterator iterator = timeSlots.iterator(); iterator.hasNext();){
		        	 AllTimeSlots tt = (AllTimeSlots) iterator.next();
	        		 //String tmpTime = "2013-10-10 "+tt.getTime_slot()+":00.0";
		        	 String recess1Time = timeSlotsSettings.getRecess1_time().getHours()+":"+timeSlotsSettings.getRecess1_time().getMinutes();
	        		 String recess2Time = timeSlotsSettings.getRecess2_time().getHours()+":"+timeSlotsSettings.getRecess2_time().getMinutes();
		        	 
		        	 if( !tt.getTime_slot().equals(recess1Time) && !tt.getTime_slot().equals(recess2Time) ) {
		        		 /*for(Iterator itr = timeTable.iterator(); itr.hasNext(); ) {
		        			 TimeTable TT = (TimeTable) itr.next();
		        		 
		        		 	String tmpTime = TT.getTime_slot().getHours()+":"+TT.getTime_slot().getMinutes();*/
		        		 	
		        		 	if( /*!tmpTime.equals(tt.getTime_slot()) &&*/ !unOccupiedTimeSlots.contains(tt.getTime_slot()) && !timeTableSlots.contains(tt.getTime_slot())) {
		        		 		unOccupiedTimeSlots.add(tt.getTime_slot());
		        		 		System.out.println("timeslot Name(Unoccupied): " + tt.getTime_slot()); 
		        		 	}
		        		 }
		        	 }
	        	 }
	        	 /*for(Iterator itr = timeTable.iterator(); itr.hasNext(); ) {
        			 TimeTable TT = (TimeTable) itr.next();
        			 String tmpTime = TT.getTime_slot().getHours()+":"+TT.getTime_slot().getMinutes();
        			 for(Iterator itr1 = unOccupiedTimeSlots.iterator(); itr1.hasNext(); ) {
        				 String next = (String) itr1.next();
        				 System.out.println("next:::"+next);
        				 if(next.equals(tmpTime)&& unOccupiedTimeSlots.contains(tmpTime)) {
        					 flag=true;
        					 
        				 }
        				 if(flag) {
        					 unOccupiedTimeSlots.remove(tmpTime);
        					 flag=false;
        				 }
        			 }
	        	 }*/
	        	 
	        // }
	         
	         
	         tx.commit();
	      }catch (HibernateException e) {
	         if (tx!=null) tx.rollback();
	         e.printStackTrace(); 
	      }finally {
	         session.close(); 
	      }
	      return unOccupiedTimeSlots;
	   }
	   
	   /* Method to  READ all the alltimeslots values of a section and semester which are not occupied for the day passed*/
	   public String[] listAllRecessTimeSlots(String sectionId) {
		  Session session = HibernateUtil.openSession();
	      Transaction tx = null;
	      List timeSlots = new ArrayList();
	      String[] unOccupiedTimeSlots = new String[2];
	      TimeSlot timeSlotSettings = null;
	      int i=0;
	      
	      try{
	         tx = session.beginTransaction();
	         timeSlots = session.createQuery("FROM AllTimeSlots where section_id='"+sectionId+"'").list();
	         timeSlotSettings = (TimeSlot) session.createQuery("FROM TimeSlot where section_id='"+sectionId+"'").uniqueResult();
	         
	        	 for (Iterator iterator = timeSlots.iterator(); iterator.hasNext();){
		        	 AllTimeSlots tt = (AllTimeSlots) iterator.next();
	        		 //String tmpTime = "2013-10-10 "+tt.getTime_slot()+":00.0";
	        		 
		        	 String recess1Time = timeSlotSettings.getRecess1_time().getHours()+":"+timeSlotSettings.getRecess1_time().getMinutes();
		        	 String recess2Time = timeSlotSettings.getRecess2_time().getHours()+":"+timeSlotSettings.getRecess2_time().getMinutes();
		        	 
		        	 
		        		 if( tt.getTime_slot().equals(recess1Time) || tt.getTime_slot().equals(recess2Time)) {
		        			 unOccupiedTimeSlots[i++] = tt.getTime_slot();
		        			 System.out.println("timeslot Name(recess): " + tt.getTime_slot()); 
		        		 }
		        	 }
	         tx.commit();
	      }catch (HibernateException e) {
	         if (tx!=null) tx.rollback();
	         e.printStackTrace(); 
	      }finally {
	         session.close(); 
	      }
	      return unOccupiedTimeSlots;
	   }
	   
	   /* Method to CREATE a blank timetable excel sheet in predefined format */
	   public boolean createExcelTemplate(String sectionId, String sectionEmailId){
		   
		   TimeTableService timeTableService = new TimeTableService();
		   AddAllTimeSlotsService allTimeSlotsService = new AddAllTimeSlotsService();
		   
		   //Blank workbook
			XSSFWorkbook workbook = new XSSFWorkbook(); 
			
			//Create a blank sheet with days and timeslot headers...
			XSSFSheet sheet = workbook.createSheet("TimeTable");
		   	int rownum = 0;
			int cellnum = 0;
			
			Row row = sheet.createRow(rownum++);
			String[] days = timeTableService.getDaysHeader();
			
			// displaying section id in 0,0 cell
			Cell cell = row.createCell(cellnum++);
			cell.setCellValue(sectionId);
			
			 //Settings days header, horizontally
			for(int i=0; i<days.length; i++) {
				cell = row.createCell(cellnum++);
				cell.setCellValue(days[i]);
			}
			
			 //Create vertical timeslots header 
			String[] timeSlots = allTimeSlotsService.listAllTimeSlotsBySection(sectionEmailId);
			String[] recessTimes = allTimeSlotsService.listAllRecessTimeSlots(sectionId);
			
			for(int i=0; i<timeSlots.length-1; i++) {
				row = sheet.createRow(rownum++);
				cellnum = 0;
				cell = row.createCell(cellnum++);
				cell.setCellValue(timeSlots[i]);
				
				for(int k=0; k < recessTimes.length; k++) {
					if(timeSlots[i].equals(recessTimes[k])) {
						for(int j=0; j< days.length; j++) {
							cell = row.createCell(cellnum++);
							cell.setCellValue("RECESS");
						}
						break;						
					}
				}
				
			}
			
			 // writting the TT format in excel sheet on disk 
			try 
			{
				//Write the workbook in file system
			    FileOutputStream out = new FileOutputStream(new File("F://timetabledemo.xlsx"));
			    workbook.write(out);
			    out.close();
			    
			    System.out.println("timetabledemo.xlsx format written successfully on disk.");
			     
			} 
			catch (Exception e) 
			{
			    e.printStackTrace();
			    return false;
			}
			return true;
	   }
}
